/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tiebreak.DAL;

import com.microsoft.sqlserver.jdbc.SQLServerException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import java.sql.SQLException;
import java.util.ArrayList;
import tiebreak.BE.Member;
import tiebreak.BE.TrainingteamJoinTeam;
import tiebreak.be.Team;

/**
 *
 * @author henrikhansen
 */
public class DalcTraining {
    static Connection m_connection;
    public static ArrayList<Member> getAllCoaches() throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("SELECT * FROM `members` WHERE `iscoache`='1'"))
        {
            throw new SQLException("Could not load from member table");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getCoaches(result);
    }

    private static ArrayList<Member> getCoaches(ResultSet result) throws SQLException
    {
        ArrayList<Member> coaches = new ArrayList<Member>();
        while (result.next())
        {
            int id = result.getInt("memberid");
            String firstName = result.getString("firstname");
            String sureName = result.getString("surename");

            Member c = new Member(id, firstName, sureName);
            coaches.add(c);
        }
        return coaches;
    }

    public static ArrayList<TrainingteamJoinTeam> getAllTeamsInTraining() throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("SELECT * FROM `trainingteam` AS tt JOIN team AS t ON tt.teamid = t.id JOIN members as m ON tt.coachid = m.memberid"))
        {
            throw new SQLException("Could not load from trainingteam table");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getTrainingTeams(result);
    }


    private static ArrayList<TrainingteamJoinTeam> getTrainingTeams(ResultSet result) throws SQLException
    {
        ArrayList<TrainingteamJoinTeam> coaches = new ArrayList<TrainingteamJoinTeam>();
        while (result.next())
        {
            int id = result.getInt("teamid");
            int coacheid = result.getInt("coachid");
            String teamName = result.getString("name");
            String firstName = result.getString("firstname");
            String sureName = result.getString("surename");

            TrainingteamJoinTeam c = new TrainingteamJoinTeam(id, coacheid, teamName, firstName, sureName);
            coaches.add(c);
        }
        return coaches;
    }

    public static ArrayList<TrainingteamJoinTeam> getAllMembersByTeam(int teamID) throws SQLServerException, SQLException {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("SELECT * FROM `teammember` AS tm JOIN members AS m ON tm.memberid = m.memberid WHERE teamid ="+teamID))
        {
            throw new SQLException("Could not load from teammber table");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getMembersByTeam(result);
    }

    private static ArrayList<TrainingteamJoinTeam> getMembersByTeam(ResultSet result) throws SQLException
    {
        ArrayList<TrainingteamJoinTeam> coaches = new ArrayList<TrainingteamJoinTeam>();
        while (result.next())
        {
            String firstName = result.getString("firstname");
            String sureName = result.getString("surename");

            TrainingteamJoinTeam c = new TrainingteamJoinTeam(firstName, sureName);
            coaches.add(c);
        }
        return coaches;
    }

    public static ArrayList<Team> getTeams() throws SQLException
    {
      m_connection = (Connection) TBConnection.getConnection();
      Statement stm = (Statement) m_connection.createStatement();

      if(!stm.execute("SELECT * FROM team"))
      {
        throw new SQLException("Could not load from table");
      }
      ResultSet result = (ResultSet) stm.getResultSet();
      return getAllTeams(result);

    }

    public static void assignCoachToTeams(int teamID, int coachID, String desc) throws SQLException
    {
      m_connection = (Connection) TBConnection.getConnection();
      Statement stm = (Statement) m_connection.createStatement();

      stm.execute("INSERT INTO `trainingteam` VALUES('"+teamID+"', '"+coachID+"', '"+desc+"')");
    }

    private static ArrayList<Team> getAllTeams(ResultSet result) throws SQLException {
        ArrayList<Team> teams = new ArrayList<Team>();
        while (result.next())
        {
            String name = result.getString("name");
            int id = result.getInt("id");
            Team t = new Team(id, name);
            teams.add(t);
        }
        return teams;
    }
}